package nemeanshop;

import java.sql.*;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Ragnar
 */
public class Database 
{
    private Connection  con;
    private Statement   stmt;
    private ResultSet   rs;
    private String      feilmeld    = "";
    
    public Database() 
    {
        
    }
    
    public void connect()
    {
        try
        {
            Class.forName("com.mysql.jdbc.Driver");
            this.con = DriverManager.getConnection("jdbc:mysql://server.nemean.no/nemean_kiosk", "zebra", "nemean12toft");
        }
        catch(Exception e)
        {
            System.out.println(e.getMessage());
        }
    }
    
    public void disconnect()
    {
        
        
    }
    
    public boolean addProduct(String barcode, String prodName, double stockPrice, double sellPrice, int quantity)
    {
        try {
            this.connect();
            this.stmt = this.con.createStatement();
            int val = this.stmt.executeUpdate("INSERT INTO products VALUES('"+barcode+"', '"+prodName+"', "+stockPrice+", "+sellPrice+", "+quantity+", "+quantity+");");
            System.out.println(val);
            return (val != 0) ? true : false;
        } catch (SQLException ex) {
            Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        }
    }
    
    public boolean deleteProduct(String bcode)
    {
        try
        {
            this.connect();
            this.stmt = this.con.createStatement();
            int val = this.stmt.executeUpdate("DELETE FROM `products` WHERE `barcode` = '"+bcode+"' LIMIT 1");
            System.out.println(val);
            return (val != 0) ? true : false;
        } catch(SQLException ex) {
            Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        }
    }
    
    public boolean updateProductQuantity(String bcode, int quantity)
    {
        try
        {
            this.connect();
            this.stmt = this.con.createStatement();
            int val = this.stmt.executeUpdate("UPDATE `products` SET `quantity` = quantity + "+quantity+" WHERE `barcode` = '"+bcode+"' LIMIT 1");
            System.out.println(val);
            return (val != 0) ? true : false;
        } catch(SQLException ex) {
            Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        }
    }
    
    public Product getProduct(String barcode)
    {
        try {
            Product p;
            this.connect();
            this.stmt = this.con.createStatement();
            this.rs = this.stmt.executeQuery("SELECT * FROM products WHERE barcode = '"+barcode+"'");
            if(this.rs.next())
            {
                p = new Product(this.rs.getString("barcode"), this.rs.getString("productName"), this.rs.getDouble("stockPrice"), this.rs.getDouble("sellPrice"), this.rs.getInt("quantity"));
            }
            else
            {
                p = null;
            }
            return p;
            
        } catch (SQLException ex) {
            Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        }
    }
    
    public void query()
    {
        
    }
    
    public ArrayList<Product> getAllProducts(){
        ArrayList<Product> p = new ArrayList<Product>();
        try {
            this.connect();
            this.stmt = this.con.createStatement();
            this.rs = this.stmt.executeQuery("SELECT * FROM products");
            
            while(this.rs.next()){
                Product prod = new Product(this.rs.getString("barcode"), this.rs.getString("productName"), this.rs.getDouble("stockPrice"), this.rs.getDouble("sellPrice"), this.rs.getInt("quantity"));
                p.add(prod);
            }
            return p;
        } catch (SQLException ex) {
            Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        }
    }
    
    
    
    //  METODEN ER IKKE TESTET!!
    
    public boolean addCashier(String barcode, int user_id){
        try {
            this.connect();
            this.stmt = this.con.createStatement();
            int val = this.stmt.executeUpdate("INSERT INTO cashier VALUES('"+barcode+"', '"+user_id+"');");
            System.out.println(val);
            return (val != 0) ? true : false;
        
        } catch (SQLException ex) {
            Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        }
    }
    
   
    
    
    public ArrayList<Cashier> getAllCashiers(){
        
        ArrayList<Cashier> c = new ArrayList<Cashier>();
        try {
            this.connect();
            this.stmt = this.con.createStatement();
            this.rs = this.stmt.executeQuery("select * from users, cashier where users.user_id = cashier.user_id");
            while(this.rs.next()){
                Cashier cash = new Cashier(this.rs.getString("barcode"), this.rs.getInt("user_id"), new Navn(this.rs.getString("firstname"), this.rs.getString("lastname")));
                
                c.add(cash);
            }
            return c;
        } catch (SQLException ex) {
            Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
            System.out.println(ex);
            return null;
        }
    }
    

      
      public boolean userExists(String bc){
        boolean finnes = false;
        if(bc != null){
            try {
                this.connect();
                stmt = con.createStatement();
                rs = stmt.executeQuery("SELECT * FROM cashier WHERE barcode = '"+bc+"'");
                
                while(rs.next()){
                    finnes = true;
                }
            
            }catch (SQLException e) {
                
                feilmeld    = " " +e;
                finnes      = false;
            }
        }
        return finnes;
    }
      
      
      public Navn getNavn(int id){
          
           try {
            Navn n;
            this.connect();
            this.stmt = this.con.createStatement();
            this.rs = this.stmt.executeQuery("SELECT lastname, firstname FROM users WHERE userID = '"+id+"'");
            
            if(this.rs.next())
            {
                n = new Navn(this.rs.getString("lastname"), this.rs.getString("firstname"));
            }
            else
            {
                n = null;
            }
            return n;
        
           } catch (SQLException ex) {
            Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        } 
      }
      
      
      public boolean registerOrder(Order o){
          
          if(o!=null){
              
              try{
                  this.connect();
                  
                  this.stmt = this.con.createStatement();
                  int val2  = this.stmt.executeUpdate("INSERT INTO cashier_order VALUES (0 ,'"+o.getCashier().getUserID()+"', now());");
                  this.rs = this.stmt.executeQuery("SELECT LAST_INSERT_ID()");
                  this.rs.next();
                  int saleID = this.rs.getInt(1);
                 
                  for(int i = 0; i<o.getBasket().size(); i++)
                  {
                      if (o.getBasket().get(i).getTotal() > 1)
                      {
                          for (int j = 0; j < o.getBasket().get(i).getTotal(); j++)
                          {
                              this.stmt = this.con.createStatement();
                              this.stmt.executeUpdate("INSERT INTO order_product VALUES('"+o.getBasket().get(i).getBarcode()+"', '"+saleID+"');");
                              this.stmt.executeUpdate("UPDATE products SET quantity = quantity -1 WHERE barcode = '"+o.getBasket().get(i).getBarcode()+"';");
                          }     
                      }
                      else
                      {
                          this.stmt = this.con.createStatement();
                            this.stmt.executeUpdate("INSERT INTO order_product VALUES('"+o.getBasket().get(i).getBarcode()+"', '"+saleID+"');");
                            this.stmt.executeUpdate("UPDATE products SET quantity = quantity -1 WHERE barcode = '"+o.getBasket().get(i).getBarcode()+"';");
                      }
                  }
                  
                  return true;
              }
              
              catch(SQLException ex){
                  System.out.println(ex.getMessage());
              }
              
              return true;
          }
          else return false;
      }
      
      
      public boolean registerAdminOrder(Order o, int id){
          
          if(o!=null){
              
              try{
                  this.connect();
                  
                  for(int i = 0; i<o.getBasket().size(); i++)
                  {
                      if (o.getBasket().get(i).getTotal() > 1)
                      {
                          for (int j = 0; j < o.getBasket().get(i).getTotal(); j++)
                          {
                              this.stmt = this.con.createStatement();
                              this.stmt.executeUpdate("INSERT INTO product_cashier VALUES('"+id+"', '"+o.getBasket().get(i).getBarcode()+"');");
                              this.stmt.executeUpdate("UPDATE products SET quantity = quantity -1 WHERE barcode = '"+o.getBasket().get(i).getBarcode()+"';");
                          }     
                      }
                      else
                      {
                          this.stmt = this.con.createStatement();
                            this.stmt.executeUpdate("INSERT INTO product_cashier VALUES('"+id+"', '"+o.getBasket().get(i).getBarcode()+"');");
                            this.stmt.executeUpdate("UPDATE products SET quantity = quantity -1 WHERE barcode = '"+o.getBasket().get(i).getBarcode()+"';");
                      }
                  }
                  
                  return true;
              }
              
              catch(SQLException ex){
                  System.out.println(ex.getMessage());
              }
              
              return true;
          }
          else return false;
      }
             
      
      
      public String logIn(String un, String pw){
          
          String s = "";
            if(!"".equals(un) || !"".equals(pw)){
               
                try {
                    this.connect();
                    stmt = con.createStatement();
                    rs = stmt.executeQuery("SELECT * FROM users, cashier WHERE username = '"+un+"' and password =sha1('"+pw+"') and cashier.user_id = users.user_id");

                    while(rs.next()){
                        System.out.println(rs.getString("username"));
                        s = rs.getString("barcode");
                    }
                }catch (SQLException e) {
                    feilmeld=""+e;
                    s = "";
                }
        }
        return s;
          
      }     
      
      public double getProfit(){
          
          try {
                this.connect();
                stmt = con.createStatement();
                rs = stmt.executeQuery("SELECT SUM(sellPrice-stockPrice) as 'Netto' FROM products, cashier_order, order_product, cashier where products.barcode = order_product.barcode and order_product.sale_id = cashier_order.sale_id and cashier_order.user_id = cashier.user_id");
                
                while(rs.next()){
                    double d = rs.getDouble("Netto");
                    return d;
                }
            }catch (SQLException e) {
                feilmeld=""+e;
                return -1;
            }
          return -1;
      }
      
      public double getUserProfit(int id){
          
          try {
                this.connect();
                stmt = con.createStatement();
                rs = stmt.executeQuery("SELECT SUM(sellPrice-stockPrice) as 'Netto' FROM products, cashier_order, order_product, cashier where products.barcode = order_product.barcode and order_product.sale_id = cashier_order.sale_id and cashier_order.user_id = '"+id+"'and cashier.user_id = '"+id+"');");
                System.out.println(rs.getDouble("Netto"));
                while(rs.next()){
                    double d = rs.getDouble("Netto");
                    return d;
                }
            }catch (SQLException e) {
                feilmeld=""+e;
                System.out.println(e);
                return -1;
            }
          return -1;
      }
}
